import pymysql

class Db:
    def __init__(self, db_config):
        self.conn = pymysql.connect(host=db_config['host'], port=db_config.get('port'), user=db_config.get('username'), password=db_config.get('password'), database=db_config.get('database'))
        self.cursor = self.conn.cursor()

    def getRows(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def getRow(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchone() # fetchone() method returns only one row

    def getOne(self, query):
        one = self.getRow(query)
        if one is None:
            return None
        return one[0] # fetchone() method returns only one row

    def getCol(self, query):
        self.cursor.execute(query)
        data = self.cursor.fetchall()
        return [(row[0],) for row in data]

    def insert(self, table, data, insertId = False):
        keys = '`,`'.join(data.keys())
        values = ','.join([f"'{value}'" for value in data.values()])
        sql = f"INSERT INTO `{table}` (`{keys}`) VALUES ({values})"
        self.cursor.execute(sql)
        self.conn.commit()
        return self.cursor.lastrowid if insertId else None

    def query(self, query):
        self.cursor.execute(query)
        self.conn.commit()
        return True

    def update(self, table, data, condition, limit = None):
        """
        Update rows in a table based on a condition.

        Args:
        table (str): The name of the table to update.
        data (dict): A dictionary containing the column names and their new values.
        condition (str): The condition to use to determine which rows to update.
        linit (int): limit number
        Returns:
        bool: True if the update was successful, False otherwise.
        """
        set_clause = ', '.join([f"`{key}` = '{value}'" for key, value in data.items()])
        sql = f"UPDATE `{table}` SET {set_clause} WHERE {condition}" + (f" LIMIT {limit}" if limit is not None else "")
        self.cursor.execute(sql)
        self.conn.commit()
        return True

    def updateRow(self, table, data, condition):
        return self.update(table, data, condition, 1)

    def close_connections(self):
        # Close Connections
        self.cursor.close()
        self.conn.close()

    def __del__(self):
        # Magic method to close connections when instance is destroyed
        self.close_connections()


"""
事务的用法
db = Db.Db()
db.cursor.execute("START TRANSACTION")
try:
    db.cursor.execute(sql1)
    db.cursor.execute(sql2)
    db.conn.commit()
except:
    db.conn.rollback()
"""